from car.hive.hive_data import HiveUtils
from car.mysqql_connect.connect import MySQLUtils


def get_all_sales():
    print("正在按月份统计销售量")
    # 实例化 HiveUtils 类
    hive_utils = HiveUtils(host='192.168.1.157', port=10000, database='default', auth_mechanism='PLAIN')
    mysql_utils = MySQLUtils(host='localhost', port=3306, user='root', password='root', database='carhouse')
    mysql_utils.connect()
    sales_by_year = hive_utils.sum_sales_by_year('all_sales_base_info_2')
    # 准备插入数据到MySQL的逻辑
    insert_query = "INSERT INTO sales_data (year, total_sales) VALUES (%s, %s)"
    for year, total_sales in sales_by_year:
        print(year, total_sales)
        try:
            mysql_utils.execute_insert(insert_query, (year, total_sales))
        except Exception as e:
            print(f"Error inserting into MySQL : {e}")
    print("数据已成功存入MySQL")
    # 关闭连接
    hive_utils.close_connection()
    mysql_utils.close_connection()

# get_all_sales()

def sum_sales_by_car():
    print("正在按月份统计销售量")
    # 实例化 HiveUtils 类
    hive_utils = HiveUtils(host='192.168.1.161', port=10000, database='default', auth_mechanism='PLAIN')
    mysql_utils = MySQLUtils(host='localhost', port=3306, user='root', password='root', database='carhouse')
    mysql_utils.connect()
    sales_by_year = hive_utils.sum_sales_by_year('all_sales_base_info_2')
    # 准备插入数据到MySQL的逻辑
    insert_query = "INSERT INTO sum_sales_by_car (sales, car) VALUES (%s, %s)"
    for car,total_sales in sales_by_year:
        print(total_sales, car)
        try:
            mysql_utils.execute_insert(insert_query, ( total_sales,car))
        except Exception as e:
            print(f"Error inserting into MySQL : {e}")
    print("数据已成功存入MySQL")
    # 关闭连接
    hive_utils.close_connection()
    mysql_utils.close_connection()

# sum_sales_by_car()



def sum_sales_by_automobile():
    print("正在按月份统计厂商销量")
    # 实例化 HiveUtils 类
    hive_utils = HiveUtils(host='192.168.1.161', port=10000, database='default', auth_mechanism='PLAIN')
    mysql_utils = MySQLUtils(host='localhost', port=3306, user='root', password='root', database='carhouse')
    mysql_utils.connect()
    sales_by_year = hive_utils.sum_sales_by_automobile('all_sales_base_info_2')
    # 准备插入数据到MySQL的逻辑
    insert_query = "INSERT INTO sum_automobilesales_by_car (date_str,sales, automobile) VALUES (%s,%s, %s)"
    for date_str,total_sales,automobile in sales_by_year:
        print(total_sales, automobile)
        try:
            mysql_utils.execute_insert(insert_query, ( date_str,total_sales,automobile))
        except Exception as e:
            print(f"Error inserting into MySQL : {e}")
    print("数据已成功存入MySQL")
    # 关闭连接
    hive_utils.close_connection()
    mysql_utils.close_connection()


sum_sales_by_automobile()






